/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ps.packdescription.excel;

import com.ps.packdescription.db.DBManager;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URL;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.commons.configuration.Configuration;
import org.apache.commons.configuration.ConfigurationException;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.htmlcleaner.CleanerProperties;
import org.htmlcleaner.HtmlCleaner;
import org.htmlcleaner.PrettyXmlSerializer;
import org.htmlcleaner.TagNode;

/**
 *
 * @author srkrishnan
 */
public class ExcelWriter {

    static String DB_PREFIX = "";
    static Connection conn = null;
    static Workbook wb = null;
    static FileOutputStream fos = null;

    public static void init() {
        try {
            Configuration config = new PropertiesConfiguration("my.properties");
            DB_PREFIX = config.getString("db.prefix");
        } catch (ConfigurationException ex) {
            Logger.getLogger(ExcelWriter.class.getName()).log(Level.SEVERE, null, ex);
        }

        wb = new HSSFWorkbook();
    }

    public static void write() {
        try {
            init();
            conn = DBManager.getConnection();

            // our SQL SELECT query. 
            // if you only need a few columns, specify them by name instead of using "*"
            String query = "select * from ps_lang";

            // create the java statement
            Statement st = conn.createStatement();

            // execute the query, and get a java resultset
            ResultSet rs = st.executeQuery(query);

            // iterate through the java resultset
            while (rs.next()) {

                Sheet sheet = wb.createSheet(rs.getString("name"));
                fillColumns(sheet);
                //initReadCategories(1, sheet);
                writeProduct(1, rs.getInt("id_lang"), sheet);
            }

        } catch (SQLException ex) {
            Logger.getLogger(ExcelWriter.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                fos = new FileOutputStream("sheet.xls");
                wb.write(fos);
                fos.close();
            } catch (IOException ex) {
                Logger.getLogger(ExcelWriter.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
    }

    private static void initReadCategories(int langid, Sheet sheet) {
        try {
            // our SQL SELECT query. 
            // if you only need a few columns, specify them by name instead of using "*"
            String query = "select c.id_category,cl.name "
                    + "from ps_category c,ps_category_lang cl "
                    + "where c.id_category=cl.id_category and cl.id_lang=" + langid;

            // create the java statement
            Statement st = conn.createStatement();

            // execute the query, and get a java resultset
            ResultSet rs = st.executeQuery(query);



            // iterate through the java resultset
            while (rs.next()) {
                int categoryid = rs.getInt("id_category");
                String categoryname = rs.getString("name");

                // print the results
                System.out.format("%d %s\n", categoryid, categoryname);
                //write to excel
                Row row = sheet.createRow(sheet.getLastRowNum() + 1);

                CellStyle style = wb.createCellStyle();
                style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
                style.setFillPattern(CellStyle.SOLID_FOREGROUND);

                Cell cell = row.createCell(0);
                cell.setCellValue(categoryid);
                cell.setCellStyle(style);
                cell = row.createCell(1);
                cell.setCellValue(categoryname);
                cell.setCellStyle(style);
                //write products of the current category
                writeProducts(categoryid, langid, sheet);
            }
            st.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

    }

    private static void writeProducts(int categoryid, int langid, Sheet sheet) {
        try {
            // our SQL SELECT query. 
            // if you only need a few columns, specify them by name instead of using "*"
            String query = "select * "
                    + "from ps_category_product "
                    + "where ps_category_product.id_category=" + categoryid;

            // create the java statement
            Statement st = conn.createStatement();

            // execute the query, and get a java resultset
            ResultSet rs = st.executeQuery(query);

            // iterate through the java resultset
            while (rs.next()) {
                int id = rs.getInt("id_category");
                int productid = rs.getInt("id_product");

                // print the results
                System.out.format("%d %d\n", id, productid);
                //write to excel

                writeProduct(productid, langid, sheet);
            }
            st.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    private static void writeProduct(int productid, int langid, Sheet sheet) {
        try {
            // our SQL SELECT query. 
            // if you only need a few columns, specify them by name instead of using "*"
//            String query = "select * "
//                    + "from ps_product_lang "
//                    + "where ps_product_lang.id_product=" + productid + " and ps_product_lang.id_lang=" + langid;

            String query = "select ps_product.reference,ps_product_lang.* "
                    + "from ps_product,ps_product_lang "
                    + "where  ps_product_lang.id_lang=" + langid + " and ps_product.id_product = ps_product_lang.id_product";

            // create the java statement
            Statement st = conn.createStatement();

            // execute the query, and get a java resultset
            ResultSet rs = st.executeQuery(query);

            // iterate through the java resultset
            while (rs.next()) {
                String reference = rs.getString("reference");
                int id_product = rs.getInt("id_product");
                int id_lang = rs.getInt("id_lang");
                String description = rs.getString("description");
                String description_short = rs.getString("description_short");
                String link_rewrite = rs.getString("link_rewrite");
                String meta_description = rs.getString("meta_description");
                String meta_keywords = rs.getString("meta_keywords");
                String meta_title = rs.getString("meta_title");
                String name = rs.getString("name");
                String available_now = rs.getString("available_now");
                String available_later = rs.getString("available_later");
                String pack_description = rs.getString("pack_description");

                pack_description = parse(pack_description);

                // print the results
                System.out.println(id_product + ", " + id_lang + ", " + description + ", " + description_short + ", " + link_rewrite + ", " + meta_description + ", " + meta_keywords + ", " + meta_title + ", " + name + ", " + available_now + ", " + available_later + ", " + pack_description);
                //write to excel
                Row row = sheet.createRow(sheet.getLastRowNum() + 1);

                Cell cell = row.createCell(0);
                cell.setCellValue(reference);

                cell = row.createCell(1);
                cell.setCellValue(id_product);

                cell = row.createCell(2);
                cell.setCellValue(id_lang);

                cell = row.createCell(3);
                cell.setCellValue(description);

                cell = row.createCell(4);
                cell.setCellValue(description_short);

                cell = row.createCell(5);
                cell.setCellValue(link_rewrite);

                cell = row.createCell(6);
                cell.setCellValue(meta_description);

                cell = row.createCell(7);
                cell.setCellValue(meta_keywords);

                cell = row.createCell(8);
                cell.setCellValue(meta_title);

                cell = row.createCell(9);
                cell.setCellValue(name);

                cell = row.createCell(10);
                cell.setCellValue(available_now);

                cell = row.createCell(11);
                cell.setCellValue(available_later);

                cell = row.createCell(12);
                cell.setCellValue(pack_description);
            }
            st.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    private static void fillColumns(Sheet sheet) {
        try {
            // our SQL SELECT query. 
            // if you only need a few columns, specify them by name instead of using "*"
            String query = "DESCRIBE ps_product_lang";

            // create the java statement
            Statement st = conn.createStatement();

            // execute the query, and get a java resultset
            ResultSet rs = st.executeQuery(query);

            Row row = sheet.createRow(0);
            int cellnum = 1;
            Cell cell = row.createCell(0);
            cell.setCellValue("REFERENCE");
            // iterate through the java resultset
            while (rs.next()) {
                String field = rs.getString("Field");

                // print the results
                System.out.println("----------->" + field);
                //write to excel
                sheet.autoSizeColumn(cellnum);
                cell = row.createCell(cellnum);
                cell.setCellValue(field);
                cellnum++;
            }
            st.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    private static String parse(String pack_description) {
        try {
            CleanerProperties props = new CleanerProperties();

    // set some properties to non-default values
            props.setTranslateSpecialEntities(true);
            props.setTransResCharsToNCR(true);
            props.setOmitComments(true);

    // do parsing
            TagNode tagNode = new HtmlCleaner(props).clean(pack_description);

            TagNode[] nodelist = tagNode.getElementsByName("tr", true);
            String packdesc="";
            for(TagNode node:nodelist)
            {
                TagNode[] datalist=null;
                datalist=node.getElementsByName("th", true);
                if(datalist.length==0)
                {
                    datalist=node.getElementsByName("td", true);
                }
                for(TagNode datanode:datalist)
                {
                    packdesc+=datanode.getText()+":";
                }
                packdesc=packdesc.substring(0, packdesc.length()-1);
                packdesc+="-";
            }
            packdesc=packdesc.substring(0, packdesc.length()-1);
            return packdesc;
    // serialize to xml file
//            new PrettyXmlSerializer(props).writeToFile(
//                    tagNode, "chinadaily.xml", "utf-8");
        } catch (Exception ex) {
            Logger.getLogger(ExcelWriter.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
}
